import  pymysql
import db.config

class DB_Model:
    tab_name=""
    fileds=[]
    pk=""
    link=""
    cursor=""

    def __init__(self,tab_name):
        '''初始化表名，及对表的连接'''
        try:
            self.tab_name=tab_name
            self.link=pymysql.connect(host=db.config.HOST,db=db.config.DBNAME,user=db.config.USER,password=db.config.PASSWORD)
            self.cursor=self.link.cursor(pymysql.cursors.DictCursor)
            self.__loadFileds()
        except Exception as err:
            print("Connect DB Expection",err)

    def __loadFileds(self):
        '''收集表字段名'''
        #sql语句
        sql="show columns from %s.%s"%(db.config.DBNAME,self.tab_name)
        #执行sql语句，去收集表中字段名字
        self.cursor.execute(sql)
        data_list=self.cursor.fetchall()
        #遍历所有字段信息
        for v in data_list:
            #收集每个字段的名字
            self.fileds.append(v["Field"])
            #收集表的主键
            if v["Key"] == "PRI":
                self.pk=v["Field"]

    def findAll(self):
        '''查询表中所有数据'''
        # sql语句
        sql = "select * from %s"%(self.tab_name)

        try:
            m = self.cursor.execute(sql)
            print("获取的用户信息条数：", m)
            data_list= self.cursor.fetchall()
            return data_list

        except Exception as err:
            print("执行查询所有信息出现错误", err)
            return []


    def update(self,data={}):
        '''更新数据库中信息'''
        #传递的参数是字典格式，默认包括在里面
        #先获取所有要更新的字段名
        update_values=[]
        theId=""
        for k,v in data.items():
            if k ==self.pk:
                theId=v
                #print("%s=%s"%(k,v))
            else:
                update_values.append("%s='%s'"%(k,v))
        #组建sql语句
        sql="update %s set %s where %s=%s"%(self.tab_name,",".join(update_values),self.pk,theId)
        #print("update sql:",sql)
        try:
            #运行sql语句
            m=self.cursor.execute(sql);
            #增，删，改需要提交事务
            self.link.commit()
            #返回数据条数或者受到影响的条数
            return self.cursor.rowcount
        except Exception as err:
            print("更新数据失败，失败原因:",err)
            self.link.rollback()
            return 0

    def find(self,id):
        '''用id查找用户的信息'''
        sql = "select * from %s where %s=%d" % (self.tab_name,self.pk,id)
        try:
            m = self.cursor.execute(sql)
            print("获取的用户信息条数：", m)
            data = self.cursor.fetchone()
            return data
        except Exception as err:
            print("sql查询出现错误", err)
            return None

    def save(self,data={}):
        '''保存用户的信息，用户信息以字典形式存储'''
        items= []
        values=[]
        #print(data)
        for k, v in data.items():
                items.append(k)
                values.append(v)

        #print(items,"/n",values)
        #####怎么处理insert sql语句！！
        #先不组装sql语句的值,生成带占位符的sql语句
        sql="insert into %s(%s) values (%s)"%(self.tab_name,",".join(items),(",").join(['%s']*len(values)))
        #print("save data sql is :",sql)

        try:
            self.cursor.execute(sql,tuple(values))
            self.link.commit()
            # 获取并返回最新自增ID
            return self.cursor.lastrowid
        except Exception as err:
            print("sql存储数据出现错误", err)
            self.link.rollback()
            return 0

    def select(self,where=[],order=None,limit=None):
        '''带where条件，order条件及limit条件的查询语句'''
        sql="select * from %s"%(self.tab_name)

        if isinstance(where,list) and len(where)>0:
            sql+=" where "+" and ".join(where)
        if order is not None:
            sql+=" order by "+order

        if limit is not None:
            sql+=" limit "+limit

        try:
            self.cursor.execute(sql)
            data_list=self.cursor.fetchall()
            print("查询到用户信息条数：", self.cursor.rowcount)
            return data_list
        except Exception as err:
            print("sql存储数据出现错误", err)
            return None


    def delete(self,id):
        '''删除用户信息'''
        sql = "delete from %s  where %s='%d'" % (self.tab_name,self.pk,id)
        try:
            m = self.cursor.execute(sql)
            #print("受影响的用户信息条数：", m)
            self.link.commit()
            return m
        except Exception as err:
            print("删除出现错误", err)
            self.link.rollback()
            return None


    def __del__(self):
        '''数据库关闭操作'''
        #关闭游标
        if self.cursor is not None:
            self.cursor.close()

        #关闭数据库连接
        if self.link is not None:
            self.link.close()




















